﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
using Lm.Common.ExtensionMethods;
using DocumentFormat.OpenXml;
using System.Text.RegularExpressions;


namespace Lm.Common.Excel.OpenXml
{
    public class OpenXmlHelper : IOpenXmlHelper
    {
        protected Dictionary<int, string> SharedStringDictionary { get; set; }

        protected SpreadsheetDocument Document { get; set; }

        #region 构造，创建对象

        [Obsolete("请使用其他的构造函数", true)]
        protected OpenXmlHelper()
        { }

        protected OpenXmlHelper(SpreadsheetDocument document)
        {
            this.Document = document;
        }

        private static void InitOpen(SpreadsheetDocument document, OpenXmlHelper helper)
        {
            helper.SharedStringDictionary = new Dictionary<int, string>();
            if (document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() == 1
                &&
                document.WorkbookPart.GetPartsOfType<SharedStringTablePart>()
                           .First()
                           .SharedStringTable != null
                )
            {
                var sharedStringTable = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>()
                           .First()
                           .SharedStringTable;
                int i = 0;
                foreach (var str in sharedStringTable)
                {
                    helper.SharedStringDictionary.Add(i, str.InnerText);
                    i++;
                }
            }
        }

        private static void InitCreate(SpreadsheetDocument document)
        {
            document.AddWorkbookPart();
            document.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
            document.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

            WorkbookStylesPart workbookStylesPart1 = document.WorkbookPart.AddNewPart<WorkbookStylesPart>("workbookStylesPart1");
            workbookStylesPart1.Stylesheet = new StyleSheetGenerator().Generate();
        }

        public static IOpenXmlHelper Create(string file)
        {
            var document = SpreadsheetDocument.Create(file, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            OpenXmlHelper.InitCreate(document);
            return new OpenXmlHelper(document);
        }

        public static IOpenXmlHelper Create(System.IO.Stream stream)
        {
            var document = SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            OpenXmlHelper.InitCreate(document);
            return new OpenXmlHelper(document);
        }

        public static IOpenXmlHelper Open(string file)
        {
            var doc = (SpreadsheetDocument.Open(file, false, new OpenSettings()));
            var helper = new OpenXmlHelper(doc);
            OpenXmlHelper.InitOpen(doc, helper);
            return helper;
        }

        public static IOpenXmlHelper Open(System.IO.Stream stream)
        {
            var doc = (SpreadsheetDocument.Open(stream, false, new OpenSettings()));
            var helper = new OpenXmlHelper(doc);
            OpenXmlHelper.InitOpen(doc, helper);
            return helper;
        }

        #endregion

        public List<string> GetSheetNameList()
        {
            return this.Document.WorkbookPart.Workbook.Sheets.Elements<Sheet>().Select(t => t.Name.Value).ToList();
        }

        protected string GetCellValue(Cell cell)
        {
            if (cell.CellValue == null)
            { return null; }
            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
            {
                string value;
                if (this.SharedStringDictionary.TryGetValue(Convert.ToInt32(cell.CellValue.Text), out value))
                {
                    return value;
                }
                else { return null; }
            }
            else if (cell.DataType != null && cell.DataType == CellValues.Boolean)
            {
                return (cell.CellValue.Text != "0").ToString();
            }
            else
            {
                return cell.CellValue.Text;
            }
        }

        protected List<ExcelColumn> GetColumnList(string sheetName)
        {
            var data = GetSheetData(sheetName);
            var rows = data.Elements<Row>();
            if (rows.Count() == 0)
            { throw new Exception("表中没有数据"); }

            var columnNamesRow = rows.First();
            List<ExcelColumn> columnList = new List<ExcelColumn>();

            foreach (var cell in columnNamesRow.Elements<Cell>())
            {
                columnList.Add(
                   new ExcelColumn()
                   {
                       CellReference = cell.CellReference,
                       Name = this.GetCellValue(cell)
                   }
                  );
            }

            if (columnList.Select(t => t.Name).Distinct().Count() != columnList.Count)
            { throw new Exception("列标题有重复"); }

            return columnList;
        }

        private SheetData GetSheetData(string sheetName)
        {
            if (this.GetSheetNameList().Contains(sheetName) == false)
            { throw new Exception(sheetName + "不存在"); }
            var sheet = this.Document.WorkbookPart.Workbook.Sheets.Elements<Sheet>().Where(t => t.Name.Value == sheetName).Single();
            var sheetPart = (WorksheetPart)this.Document.WorkbookPart.GetPartById(sheet.Id);
            var data = sheetPart.Worksheet.GetFirstChild<SheetData>();
            return data;
        }

        public System.Data.DataTable GetSheetByName(string sheetName)
        {
            DataTable dtResult = new DataTable();

            var columnList = this.GetColumnList(sheetName);
            foreach (var column in columnList)
            { dtResult.Columns.Add(column.Name, typeof(string)); }

            var data = this.GetSheetData(sheetName);
            var firstRow = true;
            var rowIndexList = new List<int>();
            var cellDict = new Dictionary<string, Cell>();

            foreach (var row in data.Elements<Row>())
            {
                if (firstRow)
                {
                    firstRow = false;
                    continue;
                }
                var cellList = row.Elements<Cell>();
                bool rowIndexRecorded = false;
                foreach (var cell in cellList)
                {
                    var az = Regex.Match(cell.CellReference, "[A-Z]{1,}").Value;
                    var no = Regex.Match(cell.CellReference, "[0-9]{1,}").Value;
                    if (rowIndexRecorded == false)
                    {
                        rowIndexList.Add(no.ToInt32());
                        rowIndexRecorded = true;
                    }
                    cellDict.Add(cell.CellReference, cell);
                }
            }

            foreach (var rowIndex in rowIndexList)
            {
                var newRow = dtResult.NewRow();
                foreach (var c in columnList)
                {
                    var cellReference = c.AZ + rowIndex;
                    Cell cell;
                    if (cellDict.TryGetValue(cellReference, out cell))
                    {
                        newRow[c.Name] = this.GetCellValue(cell);
                    }
                }
                dtResult.Rows.Add(newRow);
            }
            return dtResult;
        }

        public void AddSheet(string sheetName, System.Data.DataTable sheetData)
        {
            if (this.GetSheetNameList().Contains(sheetName))
            { throw new Exception(sheetName + "已存在"); }
            uint sheetId;
            if (this.Document.WorkbookPart.Workbook.Sheets.Elements<Sheet>().Count() == 0)
            { sheetId = 1; }
            else
            {
                sheetId = this.Document.WorkbookPart.Workbook.Sheets.Elements<Sheet>()
                    .Select(t => t.SheetId.Value).Max() + 1;
            }
            this.Document.WorkbookPart.Workbook.Sheets.Append(new Sheet() { Name = sheetName, SheetId = (UInt32Value)sheetId, Id = "rId" + sheetId });
            WorksheetPart worksheetPart = this.Document.WorkbookPart.AddNewPart<WorksheetPart>("rId" + sheetId);
            worksheetPart.Worksheet = new Worksheet();
            var data = new SheetData();
            worksheetPart.Worksheet.Append(data);
            Row headerRow = new Row();
            data.Append(headerRow);
            headerRow.RowIndex = 1;
            for (int i = 0; i < sheetData.Columns.Count; i++)
            {
                headerRow.Append(this.CreateCell(1, i + 1, sheetData.Columns[i].ColumnName));
            }
            for (int i = 0; i < sheetData.Rows.Count; i++)
            {
                var row = new Row();
                row.RowIndex = Convert.ToUInt32((i + 2));
                for (int j = 0; j < sheetData.Columns.Count; j++)
                {
                    row.Append(this.CreateCell(i + 2, j + 1, sheetData.Rows[i][j]));
                }
                data.Append(row);
            }
        }

        protected Cell CreateCell(int rowIndex, int columnIndex, object value)
        {
            Cell cell = new Cell() { };
            CellValue cellValue = new CellValue();
            if (value.GetType() == typeof(DateTime))
            {
                cellValue.Text = ((DateTime)value).ToOADate().ToString();
                cell.StyleIndex = 0;
            }
            else
            {
                cellValue.Text = value.ToString();
                cell.DataType = CellValues.String;
            }
            cell.Append(cellValue);
            cell.CellReference = AZConvert.ToAZ(columnIndex).ToString() + rowIndex;
            return cell;
        }

        public void DeleteSheet(string sheetName)
        {
            if (this.GetSheetNameList().Contains(sheetName))
            {
                var sheet = this.Document.WorkbookPart.Workbook.Sheets.Elements<Sheet>().Where(t => t.Name.Value == sheetName).Single();
                this.Document.WorkbookPart.DeletePart(sheet.Id);
                sheet.Remove();
            }
        }

        public void Dispose()
        {
            this.Document.Close();
            this.Document.Dispose();
        }
    }
}
